﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace SalariesApp
{
    public static class DBHelper
    {
        //private static SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString);
        private static SqlConnection con = new SqlConnection("Initial Catalog=KSSalaries;Data Source=182.18.135.133; User ID=KSSalaries_User;Password=KSSalaries_User_Ver2");
        private static string kukatpallyCon = string.Empty;//System.Configuration.ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ConnectionString;
        private static string miyapurCon = string.Empty;//System.Configuration.ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ConnectionString;
        private static string ameerpetCon = string.Empty;//System.Configuration.ConfigurationManager.ConnectionStrings["AmeerpetConnectionString"].ConnectionString;
        private static string jntuCon = string.Empty;//System.Configuration.ConfigurationManager.ConnectionStrings["JNTUConnectionString"].ConnectionString;
        private static string chandanagarCon = string.Empty;//System.Configuration.ConfigurationManager.ConnectionStrings["ChandanagarConnectionString"].ConnectionString;

        //Fetch all Branches
        internal static DataTable FetchBranches()
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT BranchID AS BranchID, Branch FROM Branches with (NOLOCK)", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch all Employees By Branch And Department
        internal static DataTable FetchEmployeesByBranchandDept(string branchID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT EmpID, EmpName, CONVERT(VARCHAR(10), [JoiningDate], 103) AS JoiningDate, E.Department AS DeptID, D.Department, E.Branch AS BranchID, B.Branch, Salary, E.Designation AS DesignationID, De.Designation, E.IsLeavesConsidered, CAST(EmpID AS VARCHAR(5)) + ' - ' + EmpName AS EmpIDName FROM Employees E with (NOLOCK) JOIN Branches B with (NOLOCK) ON E.Branch = B.BranchID JOIN Departments D with (NOLOCK) ON E.Department = D.DeptID JOIN Designations De with (NOLOCK) ON De.DesignationID = E.Designation WHERE E.IsValid = 1 AND E.Branch = {0} AND IsLeft = 0 ORDER BY EmpID ", branchID), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch all Employees By Branch And Department
        internal static DataTable FetchEmployeesByName(string name, bool isValidEmployees)
        {
            try
            {
                string str = "SELECT EmpID, EmpName, CONVERT(VARCHAR(10), [JoiningDate], 103) AS JoiningDate, E.Department AS DeptID, D.Department, E.Branch AS BranchID, B.Branch, E.Designation AS DesignationID, De.Designation, Salary, IsPFIncluded, PhoneNo, MobileNo, Address, ReferredBy, DocumentSubmitted, E.IsLeavesConsidered, E.IsLeft, CAST(EmpID AS VARCHAR(5)) + ' - ' + EmpName AS EmpIDName, PFAmount, Allowance, TenDaysSalary FROM Employees E with (NOLOCK) JOIN Departments D with (NOLOCK) ON E.Department = D.DeptID JOIN Branches B with (NOLOCK) ON E.Branch = B.BranchID JOIN Designations De with (NOLOCK) ON De.DesignationID = E.Designation WHERE IsValid = 1 AND (EmpName like '{0}%' OR EmpID like '{0}%') ";
                if (isValidEmployees)
                    str += " AND IsLeft = 0 ";
                str += "ORDER BY EmpID";
                using (SqlCommand cmd = new SqlCommand(string.Format(str, name), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch all Departments
        internal static DataTable FetchDepartments()
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT DeptID, Department FROM Departments with (NOLOCK)", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Checking whether Salary is Paid or Not
        internal static DataTable CheckWhetherSalaryPaid(string empID, DateTime monthYear)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM SalaryPaymentDetails with (NOLOCK) WHERE EmpID = {0} AND SalaryMonth = '{1}';", empID, monthYear.ToString("yyyy-MM-dd")), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                        else
                            return null;
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch Transaction Details For Paid
        internal static DataSet FetchTransactionDetailsForPaid(string empID, DateTime monthYear)
        {
            try
            {
                //Advance Details
                StringBuilder strBldr = new StringBuilder(string.Format("SELECT DebitTransactionID AS AdvanceID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, Amount, Branch FROM DebitTransactions D WITH (NOLOCK) LEFT OUTER JOIN Branches B ON D.FromBranch = B.BranchID WHERE TransactionType = 1 AND EmpID = {0} AND Date >= '{1}' AND Date <= '{2}';", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                //Leave Details
                strBldr.Append(string.Format("SELECT CreditTransactionID AS LeaveID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, IsHalfDay AS HalfDay FROM CreditTransactions WHERE EmpID = {0} AND TransactionType = 1 AND Date >= '{1}' AND Date <= '{2}';", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                //OT Details
                strBldr.Append(string.Format("SELECT CreditTransactionID AS 'O.T ID', TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, IsHalfDay AS HalfDay FROM CreditTransactions WITH (NOLOCK) WHERE EmpID = {0} AND TransactionType = 2 AND Date >= '{1}' AND Date <= '{2}';", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                //Bill Details
                strBldr.Append(string.Format("SELECT DebitTransactionID AS BillID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, Amount, Branch FROM DebitTransactions D WITH (NOLOCK) LEFT OUTER JOIN Branches B ON D.FromBranch = B.BranchID WHERE TransactionType = 2 AND EmpID = {0} AND Date >= '{1}' AND Date <= '{2}';", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                //Loan Issued Amount
                /*strBldr.Append(string.Format("SELECT E.LoanID, D.IssuedAmount FROM Employees E WITH (NOLOCK) JOIN LoanDetails D WITH (NOLOCK) ON E.LoanID = D.LoanID WHERE E.EmpID = {0}", empID));
                //Loan Repayed Amount
                strBldr.Append(string.Format("SELECT SUM(Amount) AS RepayedAmt FROM LoanPaymentDetails L WITH (NOLOCK) JOIN Employees E WITH (NOLOCK) ON E.LoanID = L.LoanID WHERE E.EmpID = {0}", empID));
                */

                using (SqlCommand cmd = new SqlCommand(strBldr.ToString(), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch Transaction Details For UnPaid
        internal static DataSet FetchTransactionDetailsForUnPaid(string empID, DateTime monthYear)
        {
            try
            {
                //Advance Details
                StringBuilder strBldr = new StringBuilder(string.Format("SELECT ROW_NUMBER() OVER (ORDER BY DebitTransactionID) As SrNo, DebitTransactionID AS AdvanceID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, Amount, IsValid AS Valid, Branch FROM DebitTransactions D WITH (NOLOCK) LEFT OUTER JOIN Branches B ON D.FromBranch = B.BranchID WHERE TransactionType = 1 AND IsValid = 1 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                //Leave Details
                strBldr.Append(string.Format("SELECT ROW_NUMBER() OVER (ORDER BY CreditTransactionID) As SrNo, CreditTransactionID AS LeaveID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, IsHalfDay AS 'Half Day', IsValid AS Valid FROM CreditTransactions WHERE EmpID = {0} AND TransactionType = 1 AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                //OT Details
                strBldr.Append(string.Format("SELECT ROW_NUMBER() OVER (ORDER BY CreditTransactionID) As SrNo, CreditTransactionID AS 'O.T ID', TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, IsHalfDay AS 'Half Day', IsValid AS Valid FROM CreditTransactions WITH (NOLOCK) WHERE EmpID = {0} AND TransactionType = 2 AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                //Bill Details
                strBldr.Append(string.Format("SELECT DebitTransactionID AS BillID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, Amount, Branch FROM DebitTransactions D WITH (NOLOCK) LEFT OUTER JOIN Branches B ON D.FromBranch = B.BranchID WHERE TransactionType = 2 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
                strBldr.Append(string.Format("SELECT EmpID, EmpName, JoiningDate, E.Department AS DeptID, D.Department, E.Branch AS BranchID, B.Branch, Salary, E.IsPFIncluded, E.IsLeavesConsidered, E.IsLeft, PFAmount, Allowance FROM Employees E with (NOLOCK) JOIN Departments D with (NOLOCK) ON E.Department = D.DeptID JOIN Branches B with (NOLOCK) ON E.Branch = B.BranchID WHERE EmpID = {0};", empID));
                strBldr.Append(string.Format("SELECT L.LoanID, L.IssuedAmount FROM LoanDetails L WITH (NOLOCK) WHERE L.EmpID = {0} AND IsPaidCompletely = 0;", empID));
                strBldr.Append(string.Format("SELECT SUM(Amount) AS RepayedAmt FROM LoanPaymentDetails L WITH (NOLOCK) WHERE LoanID IN (SELECT LoanID FROM LoanDetails L WITH (NOLOCK) WHERE L.EmpID = {0} AND IsPaidCompletely = 0);", empID));

                using (SqlDataAdapter adapter = new SqlDataAdapter(strBldr.ToString(), con))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch Loan Repayment Amount
        internal static string FetchRepayedAmount(string empID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT (SELECT D.IssuedAmount FROM Employees E WITH (NOLOCK) JOIN LoanDetails D WITH (NOLOCK) ON E.LoanID = D.LoanID WHERE E.EmpID = {0}) - (SELECT SUM(Amount) AS RepayedAmt FROM LoanPaymentDetails L WITH (NOLOCK) JOIN Employees E WITH (NOLOCK) ON E.LoanID = L.LoanID WHERE E.EmpID = {0}) AS Balance", empID), con))
                {
                    cmd.CommandType = CommandType.Text;
                    con.Open();
                    return cmd.ExecuteScalar().ToString();
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
            }
            return "0";
        }

        //Pay Salary based on the Employee ID
        internal static int PaySalary(string empID, string loanID, string salaryMonth, string salary, string advance, string leaves, string ot, string bills, string loanAmt, string pfAmt, string finalSalary, string deductedBillAmount, string leavesCount, string otCount, string extraAmount, string extraAmountReason, string allowance, string deductedAllowance, string currentSanctionedAmt, string currentBalanceAmt)
        {
            SqlTransaction trans = null;
            try
            {
                DataSet dsLoanDetails = null;
                if (loanAmt != string.Empty && loanAmt != "0")
                {
                    //Table 1 - Issued Amount
                    //Table 2 - Paid Amount
                    dsLoanDetails = GetAllLoanDetailsByEmpID(empID);
                }


                con.Open();
                trans = con.BeginTransaction();
                using (SqlCommand cmd = new SqlCommand(string.Format("exec PaySalary {0}, {1}, '{2}', {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, '{15}', {16}, {17}, {18}, {19}", empID, loanID, salaryMonth, salary, advance, leaves, ot, bills, loanAmt, pfAmt, finalSalary, deductedBillAmount, leavesCount, otCount, extraAmount, extraAmountReason, allowance, deductedAllowance, currentSanctionedAmt, currentBalanceAmt), con, trans))
                {
                    int res = cmd.ExecuteNonQuery();
                    if (res > 0)
                    {
                        //Add Loan Details
                        if (dsLoanDetails != null && dsLoanDetails.Tables.Count == 2)
                        {
                            //Current Month Paying Amount
                            double currentPayingAmt = 0d;
                            double.TryParse(loanAmt, out currentPayingAmt);

                            foreach (DataRow row in dsLoanDetails.Tables[0].Rows)
                            {
                                //Issued Amount
                                double issuedAmt = double.Parse(row["IssuedAmount"].ToString());

                                //Paid Amount
                                double paidAmt = 0d;
                                DataRow[] rows = dsLoanDetails.Tables[1].Select("LoanID = " + row["LoanID"]); //[0]["PaidAmount"].ToString();
                                if (rows.Count() > 0)
                                {
                                    double.TryParse(rows[0]["PaidAmount"].ToString(), out paidAmt);
                                }

                                //If current month paying amount is less than remaining loan amount
                                if (currentPayingAmt < (issuedAmt - paidAmt))
                                {
                                    res = PayLoan(row["LoanID"].ToString(), empID, currentPayingAmt, false, trans);
                                    if (res > 0)
                                    {
                                        trans.Commit();
                                        return 1;
                                    }
                                    else
                                    {
                                        trans.Rollback();
                                        return -1;
                                    }
                                }
                                //If current month paying amount is more than remaining loan amount add multiple payments
                                else
                                {
                                    double remAmt = issuedAmt - paidAmt;
                                    res = PayLoan(row["LoanID"].ToString(), empID, remAmt, true, trans);
                                    if (res <= 0)
                                    {
                                        trans.Rollback();
                                        return -1;
                                    }
                                    else
                                    {
                                        currentPayingAmt -= remAmt;
                                    }
                                }
                            }
                        }

                        trans.Commit();
                        return res;
                    }
                    else
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
            }
        }

        internal static DataSet GetAllLoanDetailsByEmpID(string empID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT LoanID, IssuedAmount FROM LoanDetails WHERE EmpID = {0} AND IsPaidCompletely = 0 ORDER BY LoanID; SELECT LoanID, SUM(Amount) AS PaidAmount FROM LoanPaymentDetails WHERE LoanID IN (SELECT LoanID FROM LoanDetails WHERE EmpID = {0} AND IsPaidCompletely = 0) GROUP BY LoanID", empID), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        internal static int PayLoan(string loanID, string empID, double amount, bool isClosed, SqlTransaction trans)
        {
            using (SqlCommand cmd = new SqlCommand(string.Format("exec PayLoan {0}, {1}, {2}, {3}", loanID, empID, amount, isClosed), con, trans))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        //Get Salary & Advance Details
        /*internal static DataTable FetchCurrentMonthAdvanceDetails(string empID)
        {
            try
            {
                //Advance Details
                DateTime dtFirstDay;
                DateTime dtLastDay;
                if (DateTime.Now.Day > 10)
                {
                    dtFirstDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, 10);
                }
                else
                {
                    dtFirstDay = new DateTime(DateTime.Now.AddMonths(-1).Year, DateTime.Now.AddMonths(-1).Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 10);
                }
                StringBuilder strBldr = new StringBuilder(string.Format("SELECT Amount AS AdvanceAmt, [Date] FROM DebitTransactions WITH (NOLOCK) WHERE TransactionType = 1 AND IsValid = 1 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, dtFirstDay.ToString("MM/dd/yyyy"), dtLastDay.ToString("MM/dd/yyyy")));
                using (SqlCommand cmd = new SqlCommand(strBldr.ToString(), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }*/

        //StringBuilder strBldr = new StringBuilder(string.Format("SELECT ROW_NUMBER() OVER (ORDER BY DebitTransactionID) As SrNo, DebitTransactionID AS AdvanceID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, Amount, IsValid AS Valid FROM DebitTransactions WITH (NOLOCK) WHERE TransactionType = 1 AND IsValid = 1 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, monthYear.AddMonths(-1).AddDays(10).ToString("MM/dd/yyyy"), monthYear.AddDays(9).ToString("MM/dd/yyyy")));
        internal static DataTable FetchCurrentMonthAdvanceDetails(string empID)
        {
            try
            {
                //Advance Details
                DateTime dtFirstDay;
                DateTime dtLastDay;
                if (DateTime.Now.Day > 10)
                {
                    dtFirstDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, 10);
                }
                else
                {
                    dtFirstDay = new DateTime(DateTime.Now.AddMonths(-1).Year, DateTime.Now.AddMonths(-1).Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 10);
                }
                StringBuilder strBldr = new StringBuilder(string.Format("SELECT ROW_NUMBER() OVER (ORDER BY DebitTransactionID) As SrNo, DebitTransactionID AS AdvanceID, TransactionType, CONVERT(VARCHAR(10), [Date], 103) AS Date, Amount, IsValid AS Valid FROM DebitTransactions WITH (NOLOCK) WHERE TransactionType = 1 AND IsValid = 1 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, dtFirstDay.ToString("MM/dd/yyyy"), dtLastDay.ToString("MM/dd/yyyy")));
                using (SqlCommand cmd = new SqlCommand(strBldr.ToString(), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Get Bill Details
        internal static DataTable FetchCurrentBillDetails(string empID)
        {
            try
            {
                //Advance Details
                DateTime dtFirstDay;
                DateTime dtLastDay;
                if (DateTime.Now.Day > 10)
                {
                    dtFirstDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, 10);
                }
                else
                {
                    dtFirstDay = new DateTime(DateTime.Now.AddMonths(-1).Year, DateTime.Now.AddMonths(-1).Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 10);
                }
                StringBuilder strBldr = new StringBuilder(string.Format("SELECT SUM(Amount) AS BillAmt FROM DebitTransactions WITH (NOLOCK) WHERE TransactionType = 2 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0;", empID, dtFirstDay.ToString("MM/dd/yyyy"), dtLastDay.ToString("MM/dd/yyyy")));
                using (SqlCommand cmd = new SqlCommand(strBldr.ToString(), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        // PayAdvanceOrBill
        internal static int PayAdvanceOrBill(string empID, string advanceAmt, AdvanceOrBill type, DateTime dt)
        {
            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec PayAdvanceOrBill {0}, {1}, {2}, '{3}'", empID, advanceAmt, (int)type, dt.ToString("MM/dd/yyyy")), con, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
            }
        }

        //Get OT & Leave Details 
        internal static DataSet FetchLeaveAndOTDetails(string empID)
        {
            try
            {
                //Advance Details
                DateTime dtFirstDay;
                DateTime dtLastDay;
                if (DateTime.Now.Day > 10)
                {
                    dtFirstDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, 10);
                }
                else
                {
                    dtFirstDay = new DateTime(DateTime.Now.AddMonths(-1).Year, DateTime.Now.AddMonths(-1).Month, 11);
                    dtLastDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 10);
                }
                StringBuilder strBldr = new StringBuilder(string.Format("SELECT ROW_NUMBER() OVER (ORDER BY CreditTransactionID) As SrNo, CreditTransactionID AS ID, CONVERT(VARCHAR(10), [Date], 103) AS Date, IsHalfDay AS 'Half Day' FROM CreditTransactions (NOLOCK) WHERE TransactionType = 1 AND IsValid = 1 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0 ORDER BY CreditTransactionID; SELECT ROW_NUMBER() OVER (ORDER BY CreditTransactionID) As SrNo, CreditTransactionID AS ID, CONVERT(VARCHAR(10), [Date], 103) AS Date, IsHalfDay AS 'Half Day' FROM CreditTransactions WHERE TransactionType = 2 AND IsValid = 1 AND EmpID = {0} AND DATEDIFF(DAY,'{1}', [Date]) >= 0 AND DATEDIFF(DAY, '{2}', [Date]) <= 0 ORDER BY CreditTransactionID;", empID, dtFirstDay.ToString("MM/dd/yyyy"), dtLastDay.ToString("MM/dd/yyyy")));
                using (SqlCommand cmd = new SqlCommand(strBldr.ToString(), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        // Add Leave or OT
        internal static int AddLeaveOrOT(string empID, LeaveOrOT type, string dt, bool isHalfDay)
        {
            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddLeaveorOT {0}, {1}, '{2}', {3}", empID, (int)type, dt, isHalfDay), con, trans))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        trans.Commit();
                        return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
            }
        }

        // Add or Update Employee
        internal static int AddorUpdateEmployee(string empID, string empName, string joiningDate, string dept, string branch, string salary, int pf, string phoneNo, string mobileNo, string address, string referredBy, string documentSubmitted, string designation, int isLeavesConsidered, string isLeft, string pfAmount, string allowance)
        {
            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddOrUpdateEmployee {0}, '{1}', '{2}', {3}, {4}, {5}, {6}, '{7}', '{8}', '{9}', '{10}', '{11}', {12}, {13}, {14}, {15}, {16}", empID, empName, joiningDate, dept, branch, salary, pf.ToString(), phoneNo, mobileNo, address, referredBy, documentSubmitted, designation, isLeavesConsidered, isLeft == "True" ? 1 : 0, pfAmount == string.Empty ? "0" : pfAmount, allowance), con, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
            }
        }

        //Fetch Employee Loan Details
        internal static DataTable FetchEmployeeLoanDetails(string name)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT E.EmpID, E.EmpName, E.Salary, CONVERT(VARCHAR(10), E.[JoiningDate], 103) AS JoiningDate, E.Department AS DeptID, D.Department AS Department, B.BranchID, B.Branch, E.LoanID, L.IssuedAmount, CONVERT(VARCHAR(10), L.[IssuedDate], 103) AS IssuedDate FROM Employees E (NOLOCK) LEFT JOIN LoanDetails L (NOLOCK) ON E.LoanID = L.LoanID JOIN Departments D (NOLOCK) ON E.Department = D.DeptID JOIN Branches B (NOLOCK) ON E.Branch = B.BranchID WHERE E.EmpName like '{0}%' OR E.EmpID like '{0}%'", name), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch Employees who opted Loans currently
        internal static DataTable FetchEmployeesWithLoans()
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT E.EmpID, E.EmpName, E.Salary, CONVERT(VARCHAR(10), E.[JoiningDate], 103) AS JoiningDate, E.Department AS DeptID, D.Department AS Department, B.BranchID, B.Branch, E.LoanID, L.IssuedAmount, CONVERT(VARCHAR(10), L.[IssuedDate], 103) AS IssuedDate FROM Employees E (NOLOCK) JOIN LoanDetails L (NOLOCK) ON E.LoanID = L.LoanID JOIN Departments D (NOLOCK) ON E.Department = D.DeptID JOIN Branches B (NOLOCK) ON E.Branch = B.BranchID WHERE E.LoanID <> -1 AND E.IsLeft = 0 AND L.IsPaidCompletely = 0 ORDER BY EmpID", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch Employees who opted Loans currently
        internal static DataTable FetchEmployeesWithoutLoans()
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT E.EmpID, E.EmpName, E.Salary, CONVERT(VARCHAR(10), E.[JoiningDate], 103) AS JoiningDate, E.Department AS DeptID, D.Department AS Dept, B.BranchID, B.Branch FROM Employees E (NOLOCK) JOIN Departments D (NOLOCK) ON E.Department = D.DeptID JOIN Branches B (NOLOCK) ON E.Branch = B.BranchID WHERE E.IsLeft = 0 AND E.LoanID = -1 ORDER BY EmpID ", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Convert Advance To Loan
        internal static int ConvertAdvanceToLoan(string empID)
        {
            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec ConvertAdvanceToLoan {0}, '{1}'", empID, DateTime.Now.ToString("MM/dd/yyyy")), con, trans))
                {
                    int result = cmd.ExecuteNonQuery();
                    if (result != 0)
                    {
                        trans.Commit();
                        return result;
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
            }
        }

        //Fetch Loan By EmpID
        internal static DataTable FetchLoansByEmpID(string empID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT LoanID, IssuedAmount, (CASE IsPaidCompletely WHEN 1 THEN 'Closed' ELSE 'Active' END) AS 'Closed/Active', (CASE IsConvertedFromAdvance WHEN 1 THEN 'True' ELSE 'False' END) AS IsConverted, CONVERT(VARCHAR(10), [IssuedDate], 103) AS [IssuedDate] FROM LoanDetails (NOLOCK) WHERE EmpID = " + empID + " ORDER BY LoanID", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        //Fetch Loan Details By LoanID
        internal static DataTable FetchLoanDetailsByLoanID(string loanID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT LoanTransactionID AS ID, LA.Action, Amount, CONVERT(VARCHAR(10), [Date], 105) AS Date FROM LoanTransactions LT JOIN LoanAction LA ON LT.Action = LA.LoanActionID WHERE LoanID = " + loanID + " ORDER BY LoanTransactionID", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        internal static int ApplyLoanByEmpID(string empID, string loanAmt)
        {
            con.Open();
            SqlTransaction trans = con.BeginTransaction();

            try
            {
                using (SqlCommand cmd = new SqlCommand("exec AddLoanByEmployee " + empID + ", " + loanAmt, con, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    trans.Commit();
                    return Int32.Parse(result);
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return 0;
            }
            finally
            {
                con.Close();
            }
        }

        internal static DataTable FetchDesignations()
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT DesignationID, Designation FROM Designations with (NOLOCK)", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        internal static bool UpdateValidatingCreditTransaction(string id, bool isValidate)
        {
            con.Open();
            SqlTransaction trans = con.BeginTransaction();

            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE CreditTransactions SET IsValid = {0} WHERE CreditTransactionID = {1}", isValidate ? 1 : 0, id), con, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    trans.Commit();
                    return true;
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        internal static bool UpdateValidatingDebitTransaction(string id, bool isValidate)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            SqlTransaction trans = con.BeginTransaction();

            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE DebitTransactions SET IsValid = {0} WHERE DebitTransactionID = {1}", isValidate ? 1 : 0, id), con, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    trans.Commit();
                    return true;
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        internal static DataSet FetchSalaryPaidAndUnPaidDetails(string branchID, DateTime monthYear, bool isPaid, bool isResigned)
        {
            try
            {
                string str = string.Empty;
                if (isPaid)
                {
                    str = "SELECT S.EmpID, E.EmpName, SalaryAmount, AdvanceAmount AS Advance, LeavesAmount AS LeavesAmt, OTAmount AS OTAmt, BillsAmount AS BillsAmt, LoanAmount AS LoanAmt, FinalSalaryAmount AS SalaryPaid, IsLeft FROM SalaryPaymentDetails S JOIN Employees E ON S.EmpID = E.EmpID WHERE DATEDIFF(Day, SalaryMonth, '{1}') = 0 ";
                    if (branchID != "0")
                        str += " AND Branch = {0}";
                    if (!isResigned)
                        str += " AND IsLeft = 0";

                    str += ";SELECT SUM(FinalSalaryAmount) FROM SalaryPaymentDetails S JOIN Employees E ON S.EmpID = E.EmpID WHERE DATEDIFF(Day, SalaryMonth, '{1}') = 0 ";
                    if (branchID != "0")
                        str += " AND Branch = {0}";
                }
                else
                {
                    str = "SELECT E.EmpID, E.EmpName, E.Salary, D.Department, DE.Designation, IsLeft FROM Employees E JOIN Departments D ON E.Department = D.DeptID JOIN Designations DE ON E.Designation = DE.DesignationID WHERE EmpID NOT IN (SELECT EmpID FROM SalaryPaymentDetails WHERE E.IsLeft = 0 AND DATEDIFF(Day, SalaryMonth, '{1}') = 0) ";
                    if (branchID != "0")
                        str += " AND Branch = {0}";
                    if (!isResigned)
                        str += " AND IsLeft = 0";
                    str += " ORDER BY E.EmpID";
                }

                //if (con.State != ConnectionState.Open)
                //    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format(str, branchID, monthYear.ToString("MM-dd-yyyy")), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
                //SELECT S.EmpID, E.EmpName, SalaryAmount, AdvanceAmount AS Advance, LeavesAmount AS LeavesAmt, OTAmount AS OTAmt, BillsAmount AS BillsAmt, LoanAmount AS LoanAmt, FinalSalaryAmount AS SalaryPaid FROM SalaryPaymentDetails S JOIN Employees E ON S.EmpID = E.EmpID WHERE DATEDIFF(Day, SalaryMonth, '5-1-2011') = 0 AND Branch = 3
            }
            catch (Exception)
            {
            }
            return null;
        }

        internal static bool ConvertSomeAdvancesToLoan(string empID, DataGridView dgAdvToLoan)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            SqlTransaction trans = con.BeginTransaction();

            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    foreach (DataGridViewRow row in dgAdvToLoan.Rows)
                    {
                        if (bool.Parse(row.Cells[5].Value.ToString()))
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = string.Format("exec ConvertAdvanceToLoanByDebitID {0}, {1}, '{2}'", empID, row.Cells[1].Value.ToString(), DateTime.Now.ToString("MM/dd/yyyy"));
                            cmd.Connection = con;
                            cmd.Transaction = trans;
                            int res = cmd.ExecuteNonQuery();
                            if (res <= 0)
                                throw new Exception(string.Empty);
                        }
                    }
                    trans.Commit();
                    return true;
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        internal static DataTable FetchEmployeeDetails(string branch, bool isResigned, string year)
        {
            try
            {
                //string str = "SELECT ROW_NUMBER()  OVER (ORDER BY  E.EmpID) As SrNo, E.EmpID, E.EmpName, D.Department, De.Designation, E.Salary AS CurrentSalary, BHD.Salary, BHD.Bonus1, BHD.Bonus2, BHD.HikedSalary, (BHD.HikedSalary - BHD.Salary) AS Difference FROM Employees E JOIN Departments D ON E.Department = D.DeptID JOIN Designations De ON E.Designation = De.DesignationID LEFT OUTER JOIN (SELECT * FROM BonusHikeDetails B WHERE B.Year = '" + year + "') BHD ON E.EmpID = BHD.EmpID ";
                string str = "SELECT ROW_NUMBER()  OVER (ORDER BY  E.EmpID) As SrNo, E.EmpID, E.EmpName, D.Department, De.Designation, E.Salary AS CurrentSalary, BHD.Salary, E.Allowance, BHD.Bonus1, BHD.Bonus2, BHD.HikedSalary, (BHD.HikedSalary - BHD.Salary) AS Difference FROM Employees E JOIN Departments D ON E.Department = D.DeptID JOIN Designations De ON E.Designation = De.DesignationID LEFT OUTER JOIN (SELECT * FROM BonusHikeDetails B WHERE B.Year = '" + year + "') BHD ON E.EmpID = BHD.EmpID ";
                if (branch != "0")
                    str += "WHERE E.Branch = {0} ";

                if (isResigned)
                {
                    if (branch != "0")
                        str += "AND E.IsLeft = 1";
                    else
                        str += " WHERE E.IsLeft = 1";
                }
                else
                {
                    if (branch != "0")
                        str += "AND E.IsLeft = 0";
                    else
                        str += "WHERE E.IsLeft = 0";
                }

                str += " ORDER BY E.EmpID";

                using (SqlCommand cmd = new SqlCommand(string.Format(str, branch), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        internal static bool SaveBonusHikeDetails(DataGridViewRowCollection dgRows, string year)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            SqlTransaction trans = con.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    foreach (DataGridViewRow row in dgRows)
                    {
                        cmd.CommandType = CommandType.Text;

                        string salary = string.Empty;

                        if ((row.Cells[6].Value.ToString() == string.Empty || row.Cells[6].Value.ToString() == null || row.Cells[6].Value.ToString() == "0") &&
                            (row.Cells[8].Value.ToString() == string.Empty || row.Cells[8].Value.ToString() == null || row.Cells[8].Value.ToString() == "0") &&
                            (row.Cells[9].Value.ToString() == string.Empty || row.Cells[9].Value.ToString() == null || row.Cells[9].Value.ToString() == "0") &&
                            (row.Cells[10].Value.ToString() == string.Empty || row.Cells[10].Value.ToString() == null || row.Cells[10].Value.ToString() == "0") &&
                            (row.Cells[11].Value.ToString() == string.Empty || row.Cells[11].Value.ToString() == null || row.Cells[11].Value.ToString() == "0"))
                        {
                            continue;
                        }
                        if (row.Cells[6].Value.ToString() != string.Empty && row.Cells[6].Value.ToString() != null && row.Cells[6].Value.ToString() != "0")
                            salary = row.Cells[6].Value.ToString();
                        else
                            salary = row.Cells[5].Value.ToString();

                        cmd.CommandText = string.Format("exec AddBonusHikeDetails '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}'", row.Cells[1].Value.ToString(), year, salary, row.Cells[8].Value.ToString(), row.Cells[9].Value.ToString(), row.Cells[10].Value.ToString(), row.Cells[11].Value.ToString());
                        cmd.Connection = con;
                        cmd.Transaction = trans;
                        int res = cmd.ExecuteNonQuery();
                        if (res <= 0)
                            throw new Exception(string.Empty);
                    }
                    trans.Commit();
                    return true;
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        internal static DataTable GetEmployeesByBranch(string branch)
        {
            SqlConnection connection = null;
            try
            {
                if (branch == "0")
                    connection = con;
                else if (branch == "1")
                    connection = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    connection = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    connection = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    connection = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    connection = new SqlConnection(jntuCon);

                using (SqlCommand cmd = new SqlCommand("SELECT * FROM Employees WITH (NOLOCK)", connection))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (branch != "0")
                    connection.Dispose();
            }
            return null;
        }

        internal static int SyncEmployeesByBranch(string branch)
        {
            SqlConnection connection = null;

            try
            {
                DataTable ds = new DataTable();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT EmpID, EmpName, Department, Designation, Branch, IsValid, IsLeft FROM Employees WITH (NOLOCK)", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    connection = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    connection = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    connection = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    connection = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    connection = new SqlConnection(jntuCon);

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                SqlCommand cmdChild = new SqlCommand();

                foreach (DataRow row in ds.Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateEmployee '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}'", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), bool.Parse(row[5].ToString()) ? '1' : '0', bool.Parse(row[6].ToString()) ? '1' : '0');
                    cmdChild.CommandType = CommandType.Text;
                    cmdChild.Connection = connection;
                    cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Dispose();
            }
            return 0;
        }

        internal static DataSet GetHikeByEmpID(string empID)
        {
            try
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT HikeID, EmpID, OldSalary, NewSalary, CONVERT(VARCHAR(10), [Date], 103) AS [Date] FROM HikeSalary WHERE EmpID = {0} ORDER BY HikeID; SELECT Salary FROM Employees WHERE EmpID = {0}", empID), con))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        internal static int HikeSalary(string empID, string oldSalary, string newSalary)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec HikeSalaryForEmployee {0}, {1}, {2}", empID, oldSalary, newSalary), con))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
